--    Author    : MAYANTING
--    Name      : ADM.RPT_SUST_DLMG_TERM_PRO_DEPB.HQL
--    Functions : 表7：存款余额产品期限结构表
--    Purpose   : 报表7数据加工
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2018-06-12  MAYANTING           1.CREATE THE PROCEDURE
--
INSERT OVERWRITE TABLE ADM.RPT_SUST_DLMG_TERM_PRO_DEPB PARTITION (DATA_DATE = '20180331')
SELECT  XT.FIN_ORG_DIST
       ,XT.CCY
       ,XT.DEPOSIT_TYPE                 -- 产品类别
       ,XT.TYPE_DESC                    -- 产品类别描述
       ,SUM(XT.BAL_0_1M   )/100000000   -- 存款余额_1月以内
       ,SUM(XT.WSUM_0_1M  )/100000000   -- 余额加权值_1月以内
       ,SUM(XT.BAL_1_3M   )/100000000   -- 存款余额_1月到3月
       ,SUM(XT.WSUM_1_3M  )/100000000   -- 余额加权值_1月到3月
       ,SUM(XT.BAL_3_6M   )/100000000   -- 存款余额_3月到6月
       ,SUM(XT.WSUM_3_6M  )/100000000   -- 余额加权值_3月到6月
       ,SUM(XT.BAL_6_12M  )/100000000   -- 存款余额_6月到一年
       ,SUM(XT.WSUM_6_12M )/100000000   -- 余额加权值_6月到一年
       ,SUM(XT.BAL_1_2Y   )/100000000   -- 存款余额_1年到2年
       ,SUM(XT.WSUM_1_2Y  )/100000000   -- 余额加权值_1年到2年
       ,SUM(XT.BAL_2_3Y   )/100000000   -- 存款余额_2年到3年
       ,SUM(XT.WSUM_2_3Y  )/100000000   -- 余额加权值_2年到3年
       ,SUM(XT.BAL_3_5Y   )/100000000   -- 存款余额_3年到5年
       ,SUM(XT.WSUM_3_5Y  )/100000000   -- 余额加权值_3年到5年
       ,SUM(XT.BAL_5_999Y )/100000000   -- 存款余额_5年以上
       ,SUM(XT.WSUM_5_999Y)/100000000   -- 余额加权值_5年以上
       ,SUM(XT.BAL_ALL    )/100000000   --所有期限-存款余额
       ,SUM(XT.WSUM_ALL   )/100000000   --所有期限-加权金额
FROM (
    -- 个人+机构
    SELECT
         AREA.AREA_CODE_4                                                                AS FIN_ORG_DIST
        ,DEP.CCY                                                                         AS CCY
        ,DEP.CLIENT_TYPE                                                                 AS DEPOSIT_TYPE
        ,CASE WHEN DEP.CLIENT_TYPE='1' THEN '个人'
              WHEN DEP.CLIENT_TYPE='0' THEN '机构' END                                   AS TYPE_DESC
        ,COALESCE(CASE WHEN DEP.TERM_CODE='1' THEN DEP.ACTUAL_BAL ELSE 0 END,0)          AS BAL_0_1M      -- 存款余额_1月以内
        ,COALESCE(CASE WHEN DEP.TERM_CODE='1' THEN DEP.WSUM_BAL   ELSE 0 END,0)          AS WSUM_0_1M     -- 余额加权值_1月以内
        ,COALESCE(CASE WHEN DEP.TERM_CODE='2' THEN DEP.ACTUAL_BAL ELSE 0 END,0)          AS BAL_1_3M      -- 存款余额_1月到3月
        ,COALESCE(CASE WHEN DEP.TERM_CODE='2' THEN DEP.WSUM_BAL   ELSE 0 END,0)          AS WSUM_1_3M     -- 余额加权值_1月到3月
        ,COALESCE(CASE WHEN DEP.TERM_CODE='3' THEN DEP.ACTUAL_BAL ELSE 0 END,0)          AS BAL_3_6M      -- 存款余额_3月到6月
        ,COALESCE(CASE WHEN DEP.TERM_CODE='3' THEN DEP.WSUM_BAL   ELSE 0 END,0)          AS WSUM_3_6M     -- 余额加权值_3月到6月
        ,COALESCE(CASE WHEN DEP.TERM_CODE='4' THEN DEP.ACTUAL_BAL ELSE 0 END,0)          AS BAL_6_12M     -- 存款余额_6月到一年
        ,COALESCE(CASE WHEN DEP.TERM_CODE='4' THEN DEP.WSUM_BAL   ELSE 0 END,0)          AS WSUM_6_12M    -- 余额加权值_6月到一年
        ,COALESCE(CASE WHEN DEP.TERM_CODE='5' THEN DEP.ACTUAL_BAL ELSE 0 END,0)          AS BAL_1_2Y      -- 存款余额_1年到2年
        ,COALESCE(CASE WHEN DEP.TERM_CODE='5' THEN DEP.WSUM_BAL   ELSE 0 END,0)          AS WSUM_1_2Y     -- 余额加权值_1年到2年
        ,COALESCE(CASE WHEN DEP.TERM_CODE='6' THEN DEP.ACTUAL_BAL ELSE 0 END,0)          AS BAL_2_3Y      -- 存款余额_2年到3年
        ,COALESCE(CASE WHEN DEP.TERM_CODE='6' THEN DEP.WSUM_BAL   ELSE 0 END,0)          AS WSUM_2_3Y     -- 余额加权值_2年到3年
        ,COALESCE(CASE WHEN DEP.TERM_CODE='7' THEN DEP.ACTUAL_BAL ELSE 0 END,0)          AS BAL_3_5Y      -- 存款余额_3年到5年
        ,COALESCE(CASE WHEN DEP.TERM_CODE='7' THEN DEP.WSUM_BAL   ELSE 0 END,0)          AS WSUM_3_5Y     -- 余额加权值_3年到5年
        ,COALESCE(CASE WHEN DEP.TERM_CODE IN ('8','9') THEN DEP.ACTUAL_BAL ELSE 0 END,0) AS BAL_5_999Y    -- 存款余额_5年以上
        ,COALESCE(CASE WHEN DEP.TERM_CODE IN ('8','9') THEN DEP.WSUM_BAL   ELSE 0 END,0) AS WSUM_5_999Y   -- 余额加权值_5年以上
        ,COALESCE(DEP.ACTUAL_BAL,0)                                                      AS BAL_ALL       --所有期限-存款余额
        ,COALESCE(DEP.WSUM_BAL,0)                                                        AS WSUM_ALL      --所有期限-加权金额
    FROM (SELECT * FROM EDW.DS_DEPB_SUM WHERE DATA_DATE='20180331')DEP
    -- 与地区表最细级关联
    LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON DEP.FIN_ORG_NO=REAL.ORG_ID AND '20180331' BETWEEN REAL.START_DATE AND REAL.END_DATE
    LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '20180331' BETWEEN AREA.START_DATE AND AREA.END_DATE

    UNION ALL

    -- 个人_活期储蓄存款+定期储蓄存款+定活两便存款+通知存款+协议存款
    SELECT
         AREA.AREA_CODE_4                                                                     AS FIN_ORG_DIST
        ,DEP.CCY                                                                              AS CCY
        ,CASE WHEN DEP.DEPOSIT_TYPE='D013' THEN '11'
              WHEN DEP.DEPOSIT_TYPE='D014' THEN '12'
              WHEN DEP.DEPOSIT_TYPE='D02'  THEN '13'
              WHEN DEP.DEPOSIT_TYPE='D03'  THEN '14'
              WHEN DEP.DEPOSIT_TYPE='D04'  THEN '15'
         END                                                                                  AS DEPOSIT_TYPE
        ,CASE WHEN DEP.DEPOSIT_TYPE='D013' THEN '活期储蓄存款'
              WHEN DEP.DEPOSIT_TYPE='D014' THEN '定期储蓄存款'
              WHEN DEP.DEPOSIT_TYPE='D02'  THEN '定活两便存款'
              WHEN DEP.DEPOSIT_TYPE='D03'  THEN '通知存款'
              WHEN DEP.DEPOSIT_TYPE='D04'  THEN '协议存款'
         END                                                                                  AS TYPE_DESC
        ,COALESCE(CASE WHEN DEP.TERM_CODE='1' AND DEP.DEPOSIT_TYPE IN('D014','D04') THEN DEP.ACTUAL_BAL ELSE 0 END,0)          AS BAL_0_1M      -- 存款余额_1月以内
        ,COALESCE(CASE WHEN DEP.TERM_CODE='1' AND DEP.DEPOSIT_TYPE IN('D014','D04') THEN DEP.WSUM_BAL   ELSE 0 END,0)          AS WSUM_0_1M     -- 余额加权值_1月以内
        ,COALESCE(CASE WHEN DEP.TERM_CODE='2' AND DEP.DEPOSIT_TYPE IN('D014','D04') THEN DEP.ACTUAL_BAL ELSE 0 END,0)          AS BAL_1_3M      -- 存款余额_1月到3月
        ,COALESCE(CASE WHEN DEP.TERM_CODE='2' AND DEP.DEPOSIT_TYPE IN('D014','D04') THEN DEP.WSUM_BAL   ELSE 0 END,0)          AS WSUM_1_3M     -- 余额加权值_1月到3月
        ,COALESCE(CASE WHEN DEP.TERM_CODE='3' AND DEP.DEPOSIT_TYPE IN('D014','D04') THEN DEP.ACTUAL_BAL ELSE 0 END,0)          AS BAL_3_6M      -- 存款余额_3月到6月
        ,COALESCE(CASE WHEN DEP.TERM_CODE='3' AND DEP.DEPOSIT_TYPE IN('D014','D04') THEN DEP.WSUM_BAL   ELSE 0 END,0)          AS WSUM_3_6M     -- 余额加权值_3月到6月
        ,COALESCE(CASE WHEN DEP.TERM_CODE='4' AND DEP.DEPOSIT_TYPE IN('D014','D04') THEN DEP.ACTUAL_BAL ELSE 0 END,0)          AS BAL_6_12M     -- 存款余额_6月到一年
        ,COALESCE(CASE WHEN DEP.TERM_CODE='4' AND DEP.DEPOSIT_TYPE IN('D014','D04') THEN DEP.WSUM_BAL   ELSE 0 END,0)          AS WSUM_6_12M    -- 余额加权值_6月到一年
        ,COALESCE(CASE WHEN DEP.TERM_CODE='5' AND DEP.DEPOSIT_TYPE IN('D014','D04') THEN DEP.ACTUAL_BAL ELSE 0 END,0)          AS BAL_1_2Y      -- 存款余额_1年到2年
        ,COALESCE(CASE WHEN DEP.TERM_CODE='5' AND DEP.DEPOSIT_TYPE IN('D014','D04') THEN DEP.WSUM_BAL   ELSE 0 END,0)          AS WSUM_1_2Y     -- 余额加权值_1年到2年
        ,COALESCE(CASE WHEN DEP.TERM_CODE='6' AND DEP.DEPOSIT_TYPE IN('D014','D04') THEN DEP.ACTUAL_BAL ELSE 0 END,0)          AS BAL_2_3Y      -- 存款余额_2年到3年
        ,COALESCE(CASE WHEN DEP.TERM_CODE='6' AND DEP.DEPOSIT_TYPE IN('D014','D04') THEN DEP.WSUM_BAL   ELSE 0 END,0)          AS WSUM_2_3Y     -- 余额加权值_2年到3年
        ,COALESCE(CASE WHEN DEP.TERM_CODE='7' AND DEP.DEPOSIT_TYPE IN('D014','D04') THEN DEP.ACTUAL_BAL ELSE 0 END,0)          AS BAL_3_5Y      -- 存款余额_3年到5年
        ,COALESCE(CASE WHEN DEP.TERM_CODE='7' AND DEP.DEPOSIT_TYPE IN('D014','D04') THEN DEP.WSUM_BAL   ELSE 0 END,0)          AS WSUM_3_5Y     -- 余额加权值_3年到5年
        ,COALESCE(CASE WHEN DEP.TERM_CODE IN ('8','9') AND DEP.DEPOSIT_TYPE IN('D014','D04') THEN DEP.ACTUAL_BAL ELSE 0 END,0) AS BAL_5_999Y    -- 存款余额_5年以上
        ,COALESCE(CASE WHEN DEP.TERM_CODE IN ('8','9') AND DEP.DEPOSIT_TYPE IN('D014','D04') THEN DEP.WSUM_BAL   ELSE 0 END,0) AS WSUM_5_999Y   -- 余额加权值_5年以上
        ,COALESCE(DEP.ACTUAL_BAL,0)                                                                                            AS BAL_ALL       --所有期限-存款余额
        ,COALESCE(DEP.WSUM_BAL,0)                                                                                              AS WSUM_ALL      --所有期限-加权金额
    FROM (SELECT * FROM EDW.DS_DEPB_SUM WHERE DATA_DATE='20180331' AND CLIENT_TYPE = '1' AND DEPOSIT_TYPE IN ('D013','D02','D03','D04','D014'))DEP
    -- 与地区表最细级关联
    LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON DEP.FIN_ORG_NO=REAL.ORG_ID AND '20180331' BETWEEN REAL.START_DATE AND REAL.END_DATE
    LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '20180331' BETWEEN AREA.START_DATE AND AREA.END_DATE

    UNION ALL

    -- 个人+机构_协定存款
    SELECT
         AREA.AREA_CODE_4                                                                     AS FIN_ORG_DIST
        ,DEP.CCY                                                                              AS CCY
        ,CASE WHEN DEP.CLIENT_TYPE='1' THEN '16'
              WHEN DEP.CLIENT_TYPE='0' THEN '26'
         END                                                                                  AS DEPOSIT_TYPE
        ,'协定存款'                                                                           AS TYPE_DESC
        ,0                                                                                    AS BAL_0_1M      -- 存款余额_1月以内
        ,0                                                                                    AS WSUM_0_1M     -- 余额加权值_1月以内
        ,0                                                                                    AS BAL_1_3M      -- 存款余额_1月到3月
        ,0                                                                                    AS WSUM_1_3M     -- 余额加权值_1月到3月
        ,0                                                                                    AS BAL_3_6M      -- 存款余额_3月到6月
        ,0                                                                                    AS WSUM_3_6M     -- 余额加权值_3月到6月
        ,0                                                                                    AS BAL_6_12M     -- 存款余额_6月到一年
        ,0                                                                                    AS WSUM_6_12M    -- 余额加权值_6月到一年
        ,0                                                                                    AS BAL_1_2Y      -- 存款余额_1年到2年
        ,0                                                                                    AS WSUM_1_2Y     -- 余额加权值_1年到2年
        ,0                                                                                    AS BAL_2_3Y      -- 存款余额_2年到3年
        ,0                                                                                    AS WSUM_2_3Y     -- 余额加权值_2年到3年
        ,0                                                                                    AS BAL_3_5Y      -- 存款余额_3年到5年
        ,0                                                                                    AS WSUM_3_5Y     -- 余额加权值_3年到5年
        ,0                                                                                    AS BAL_5_999Y    -- 存款余额_5年以上
        ,0                                                                                    AS WSUM_5_999Y   -- 余额加权值_5年以上
        ,COALESCE(DEP.ACTUAL_BAL,0)                                                           AS BAL_ALL      --所有期限-存款余额
        ,COALESCE(DEP.WSUM_BAL,0)                                                             AS WSUM_ALL      --所有期限-加权金额
    FROM (SELECT * FROM EDW.DS_DEPB_SUM WHERE DATA_DATE='20180331' AND DEPOSIT_TYPE IN ('D051','D052'))DEP
    -- 与地区表最细级关联
    LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON DEP.FIN_ORG_NO=REAL.ORG_ID AND '20180331' BETWEEN REAL.START_DATE AND REAL.END_DATE
    LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '20180331' BETWEEN AREA.START_DATE AND AREA.END_DATE

    UNION ALL

    -- 个人+机构_保证金存款
    SELECT
         AREA.AREA_CODE_4                                                                     AS FIN_ORG_DIST
        ,DEP.CCY                                                                              AS CCY
        ,CASE WHEN CLIENT_TYPE='1' THEN '17'
              WHEN CLIENT_TYPE='0' THEN '27'
        END                                                                                   AS DEPOSIT_TYPE
        ,'保证金贷款'                                                                         AS TYPE_DESC
        ,0                                                                                    AS BAL_0_1M      -- 存款余额_1月以内
        ,0                                                                                    AS WSUM_0_1M     -- 余额加权值_1月以内
        ,0                                                                                    AS BAL_1_3M      -- 存款余额_1月到3月
        ,0                                                                                    AS WSUM_1_3M     -- 余额加权值_1月到3月
        ,0                                                                                    AS BAL_3_6M      -- 存款余额_3月到6月
        ,0                                                                                    AS WSUM_3_6M     -- 余额加权值_3月到6月
        ,0                                                                                    AS BAL_6_12M     -- 存款余额_6月到一年
        ,0                                                                                    AS WSUM_6_12M    -- 余额加权值_6月到一年
        ,0                                                                                    AS BAL_1_2Y      -- 存款余额_1年到2年
        ,0                                                                                    AS WSUM_1_2Y     -- 余额加权值_1年到2年
        ,0                                                                                    AS BAL_2_3Y      -- 存款余额_2年到3年
        ,0                                                                                    AS WSUM_2_3Y     -- 余额加权值_2年到3年
        ,0                                                                                    AS BAL_3_5Y      -- 存款余额_3年到5年
        ,0                                                                                    AS WSUM_3_5Y     -- 余额加权值_3年到5年
        ,0                                                                                    AS BAL_5_999Y    -- 存款余额_5年以上
        ,0                                                                                    AS WSUM_5_999Y   -- 余额加权值_5年以上
        ,COALESCE(DEP.ACTUAL_BAL,0)                                                           AS BAL_ALL      --所有期限-存款余额
        ,COALESCE(DEP.WSUM_BAL,0)                                                             AS WSUM_ALL      --所有期限-加权金额
    FROM (SELECT * FROM EDW.DS_DEPB_SUM WHERE DATA_DATE='20180331' AND DEPOSIT_TYPE IN ('D061','D062','D063','D064','D065','D066','D067','D068','D069'))DEP
    -- 与地区表最细级关联
    LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON DEP.FIN_ORG_NO=REAL.ORG_ID AND '20180331' BETWEEN REAL.START_DATE AND REAL.END_DATE
    LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '20180331' BETWEEN AREA.START_DATE AND AREA.END_DATE

    UNION ALL

    -- 机构_单位活期存款+单位定期存款+定活两便存款+通知存款+协议存款
    SELECT
         AREA.AREA_CODE_4                                                                     AS FIN_ORG_DIST
        ,DEP.CCY                                                                              AS CCY
        ,CASE WHEN DEP.DEPOSIT_TYPE='D011' THEN '11'
              WHEN DEP.DEPOSIT_TYPE='D012' THEN '12'
              WHEN DEP.DEPOSIT_TYPE='D02'  THEN '13'
              WHEN DEP.DEPOSIT_TYPE='D03'  THEN '14'
              WHEN DEP.DEPOSIT_TYPE='D04'  THEN '15'
         END                                                                                  AS DEPOSIT_TYPE
        ,CASE WHEN DEP.DEPOSIT_TYPE='D011' THEN '单位活期存款'
              WHEN DEP.DEPOSIT_TYPE='D012' THEN '单位定期存款'
              WHEN DEP.DEPOSIT_TYPE='D02'  THEN '定活两便存款'
              WHEN DEP.DEPOSIT_TYPE='D03'  THEN '通知存款'
              WHEN DEP.DEPOSIT_TYPE='D04'  THEN '协议存款'
         END                                                                                  AS TYPE_DESC
        ,COALESCE(CASE WHEN DEP.TERM_CODE='1' AND DEP.DEPOSIT_TYPE IN ('D04','D012') THEN DEP.ACTUAL_BAL ELSE 0 END,0)          AS BAL_0_1M      -- 存款余额_1月以内
        ,COALESCE(CASE WHEN DEP.TERM_CODE='1' AND DEP.DEPOSIT_TYPE IN ('D04','D012') THEN DEP.WSUM_BAL   ELSE 0 END,0)          AS WSUM_0_1M     -- 余额加权值_1月以内
        ,COALESCE(CASE WHEN DEP.TERM_CODE='2' AND DEP.DEPOSIT_TYPE IN ('D04','D012') THEN DEP.ACTUAL_BAL ELSE 0 END,0)          AS BAL_1_3M      -- 存款余额_1月到3月
        ,COALESCE(CASE WHEN DEP.TERM_CODE='2' AND DEP.DEPOSIT_TYPE IN ('D04','D012') THEN DEP.WSUM_BAL   ELSE 0 END,0)          AS WSUM_1_3M     -- 余额加权值_1月到3月
        ,COALESCE(CASE WHEN DEP.TERM_CODE='3' AND DEP.DEPOSIT_TYPE IN ('D04','D012') THEN DEP.ACTUAL_BAL ELSE 0 END,0)          AS BAL_3_6M      -- 存款余额_3月到6月
        ,COALESCE(CASE WHEN DEP.TERM_CODE='3' AND DEP.DEPOSIT_TYPE IN ('D04','D012') THEN DEP.WSUM_BAL   ELSE 0 END,0)          AS WSUM_3_6M     -- 余额加权值_3月到6月
        ,COALESCE(CASE WHEN DEP.TERM_CODE='4' AND DEP.DEPOSIT_TYPE IN ('D04','D012') THEN DEP.ACTUAL_BAL ELSE 0 END,0)          AS BAL_6_12M     -- 存款余额_6月到一年
        ,COALESCE(CASE WHEN DEP.TERM_CODE='4' AND DEP.DEPOSIT_TYPE IN ('D04','D012') THEN DEP.WSUM_BAL   ELSE 0 END,0)          AS WSUM_6_12M    -- 余额加权值_6月到一年
        ,COALESCE(CASE WHEN DEP.TERM_CODE='5' AND DEP.DEPOSIT_TYPE IN ('D04','D012') THEN DEP.ACTUAL_BAL ELSE 0 END,0)          AS BAL_1_2Y      -- 存款余额_1年到2年
        ,COALESCE(CASE WHEN DEP.TERM_CODE='5' AND DEP.DEPOSIT_TYPE IN ('D04','D012') THEN DEP.WSUM_BAL   ELSE 0 END,0)          AS WSUM_1_2Y     -- 余额加权值_1年到2年
        ,COALESCE(CASE WHEN DEP.TERM_CODE='6' AND DEP.DEPOSIT_TYPE IN ('D04','D012') THEN DEP.ACTUAL_BAL ELSE 0 END,0)          AS BAL_2_3Y      -- 存款余额_2年到3年
        ,COALESCE(CASE WHEN DEP.TERM_CODE='6' AND DEP.DEPOSIT_TYPE IN ('D04','D012') THEN DEP.WSUM_BAL   ELSE 0 END,0)          AS WSUM_2_3Y     -- 余额加权值_2年到3年
        ,COALESCE(CASE WHEN DEP.TERM_CODE='7' AND DEP.DEPOSIT_TYPE IN ('D04','D012') THEN DEP.ACTUAL_BAL ELSE 0 END,0)          AS BAL_3_5Y      -- 存款余额_3年到5年
        ,COALESCE(CASE WHEN DEP.TERM_CODE='7' AND DEP.DEPOSIT_TYPE IN ('D04','D012') THEN DEP.WSUM_BAL   ELSE 0 END,0)          AS WSUM_3_5Y     -- 余额加权值_3年到5年
        ,COALESCE(CASE WHEN DEP.TERM_CODE IN ('8','9') AND DEP.DEPOSIT_TYPE IN ('D04','D012') THEN DEP.ACTUAL_BAL ELSE 0 END,0) AS BAL_5_999Y    -- 存款余额_5年以上
        ,COALESCE(CASE WHEN DEP.TERM_CODE IN ('8','9') AND DEP.DEPOSIT_TYPE IN ('D04','D012') THEN DEP.WSUM_BAL   ELSE 0 END,0) AS WSUM_5_999Y   -- 余额加权值_5年以上
        ,COALESCE(DEP.ACTUAL_BAL,0)                                                                                             AS BAL_ALL       --所有期限-存款余额
        ,COALESCE(DEP.WSUM_BAL,0)                                                                                               AS WSUM_ALL      --所有期限-加权金额
    FROM (SELECT * FROM EDW.DS_DEPB_SUM WHERE DATA_DATE='20180331' AND CLIENT_TYPE = '0'AND DEPOSIT_TYPE IN ('D011','D012','D02','D03','D04'))DEP
    -- 与地区表最细级关联
    LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON DEP.FIN_ORG_NO=REAL.ORG_ID AND '20180331' BETWEEN REAL.START_DATE AND REAL.END_DATE
    LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '20180331' BETWEEN AREA.START_DATE AND AREA.END_DATE

    UNION ALL

    -- 个人+机构 合计
    SELECT
         AREA.AREA_CODE_4                                                                AS FIN_ORG_DIST
        ,DEP.CCY                                                                         AS CCY
        ,'3'                                                                             AS DEPOSIT_TYPE
        ,'合计'                                                                          AS TYPE_DESC
        ,COALESCE(CASE WHEN DEP.TERM_CODE='1' THEN DEP.ACTUAL_BAL ELSE 0 END,0)          AS BAL_0_1M      -- 存款余额_1月以内
        ,COALESCE(CASE WHEN DEP.TERM_CODE='1' THEN DEP.WSUM_BAL   ELSE 0 END,0)          AS WSUM_0_1M     -- 余额加权值_1月以内
        ,COALESCE(CASE WHEN DEP.TERM_CODE='2' THEN DEP.ACTUAL_BAL ELSE 0 END,0)          AS BAL_1_3M      -- 存款余额_1月到3月
        ,COALESCE(CASE WHEN DEP.TERM_CODE='2' THEN DEP.WSUM_BAL   ELSE 0 END,0)          AS WSUM_1_3M     -- 余额加权值_1月到3月
        ,COALESCE(CASE WHEN DEP.TERM_CODE='3' THEN DEP.ACTUAL_BAL ELSE 0 END,0)          AS BAL_3_6M      -- 存款余额_3月到6月
        ,COALESCE(CASE WHEN DEP.TERM_CODE='3' THEN DEP.WSUM_BAL   ELSE 0 END,0)          AS WSUM_3_6M     -- 余额加权值_3月到6月
        ,COALESCE(CASE WHEN DEP.TERM_CODE='4' THEN DEP.ACTUAL_BAL ELSE 0 END,0)          AS BAL_6_12M     -- 存款余额_6月到一年
        ,COALESCE(CASE WHEN DEP.TERM_CODE='4' THEN DEP.WSUM_BAL   ELSE 0 END,0)          AS WSUM_6_12M    -- 余额加权值_6月到一年
        ,COALESCE(CASE WHEN DEP.TERM_CODE='5' THEN DEP.ACTUAL_BAL ELSE 0 END,0)          AS BAL_1_2Y      -- 存款余额_1年到2年
        ,COALESCE(CASE WHEN DEP.TERM_CODE='5' THEN DEP.WSUM_BAL   ELSE 0 END,0)          AS WSUM_1_2Y     -- 余额加权值_1年到2年
        ,COALESCE(CASE WHEN DEP.TERM_CODE='6' THEN DEP.ACTUAL_BAL ELSE 0 END,0)          AS BAL_2_3Y      -- 存款余额_2年到3年
        ,COALESCE(CASE WHEN DEP.TERM_CODE='6' THEN DEP.WSUM_BAL   ELSE 0 END,0)          AS WSUM_2_3Y     -- 余额加权值_2年到3年
        ,COALESCE(CASE WHEN DEP.TERM_CODE='7' THEN DEP.ACTUAL_BAL ELSE 0 END,0)          AS BAL_3_5Y      -- 存款余额_3年到5年
        ,COALESCE(CASE WHEN DEP.TERM_CODE='7' THEN DEP.WSUM_BAL   ELSE 0 END,0)          AS WSUM_3_5Y     -- 余额加权值_3年到5年
        ,COALESCE(CASE WHEN DEP.TERM_CODE IN ('8','9') THEN DEP.ACTUAL_BAL ELSE 0 END,0) AS BAL_5_999Y    -- 存款余额_5年以上
        ,COALESCE(CASE WHEN DEP.TERM_CODE IN ('8','9') THEN DEP.WSUM_BAL   ELSE 0 END,0) AS WSUM_5_999Y   -- 余额加权值_5年以上
        ,COALESCE(DEP.ACTUAL_BAL,0)                                                      AS BAL_ALL       --所有期限-存款余额
        ,COALESCE(DEP.WSUM_BAL,0)                                                        AS WSUM_ALL      --所有期限-加权金额
    FROM (SELECT * FROM EDW.DS_DEPB_SUM WHERE DATA_DATE='20180331')DEP
    -- 与地区表最细级关联
    LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON DEP.FIN_ORG_NO=REAL.ORG_ID AND '20180331' BETWEEN REAL.START_DATE AND REAL.END_DATE
    LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '20180331' BETWEEN AREA.START_DATE AND AREA.END_DATE
   )XT
   GROUP BY  XT.FIN_ORG_DIST
            ,XT.CCY
            ,XT.DEPOSIT_TYPE                 -- 产品类别
            ,XT.TYPE_DESC                    -- 产品类别描述
;